/*******************************************************************************************
 * Copyright (c) 2006-2009 Alex Robson, Rob Simmons, Craig Israel, and Rachel Twyford
 *
 * Use of this software in any form requires that you have both 
 * read and agreed to the following terms:
 *
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the "Software"), 
 * to deal in the Software without restriction, including without limitation 
 * the rights to use, copy, modify, merge, publish, distribute, sublicense, 
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions: 
 *
 * Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 * Redistributions in binary form must reproduce the above copyright notice, 
 * this list of conditions and the following disclaimer in the 
 * documentation and/or other materials provided with the distribution.
 * Neither the name of Nvigorate nor the names of its contributors may 
 * be used to endorse or promote products derived from this software without 
 * specific prior written permission. 
 *
 * THIS SOFTWARE IS PROVIDED "AS IS" AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 
 * DISCLAIMED. IN NO EVENT SHALL ANY CONTRIBUTOR BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 * ****************************************************************************************/

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Nvigorate.Common;
using Nvigorate.Common.Reflection;
using Nvigorate.Data.Query;
using Nvigorate.Logging;

namespace Nvigorate.Data
{
    /// <summary>
    /// This object is intended to provide common functionality for connecting to 
    /// and obtaining and modifying data from different ADO.Net supported databases.
    /// </summary>
    public partial class DataInterface
    {
        #region Members

        private const int DEFAULT_TIMEOUT = 60;
        private bool _cache;
        private string _connectionString;
        private Dictionary<string, DataTable> _tables = new Dictionary<string, DataTable>();
        private Dictionary<string, DataSet> _dataSets = new Dictionary<string, DataSet>();
        private Dictionary<string, IDatabaseCommand> _procedures = new Dictionary<string, IDatabaseCommand>();
        private int _timeout;
        private IDatabaseProvider _provider;
        private IDatabaseCommandFactory _commandFactory;

        #endregion

        #region Properties

        /// <summary>
        /// If Cache is true, than DataInterface will cache -all- query results
        /// for later retrieval.
        /// </summary>
        public bool CacheResults
        {
            get { return _cache; }
            set { _cache = value; }
        }

        /// <summary>
        /// Handle to the IDbConnection object
        /// </summary>
        public IDbConnection Connection
        {
            get { return _provider.GetConnection(_connectionString); }
        }

        public IDatabaseProvider Provider
        {
            get { return _provider; }
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Retrieves a list of stored procedures and their parameters from the database and
        /// caches each so they can be called in the future.
        /// </summary>
        public void AddAllProcedures()
        {
            try
            {
                DataSet parameterSet = SetupCommand(_provider.GetParamsForAllProceduresCommand(),
                                                    new object[]{})
                    .GetDataSet(Connection, "parameterSet", new[] {"procs", "params"});

                DataRelation relation =
                    new DataRelation("p2p", parameterSet.Tables["procs"].Columns["Procedure_Name"],
                                     parameterSet.Tables["params"].Columns["Procedure_Name"]);

                parameterSet.Relations.Add(relation);

                foreach (DataRow row in parameterSet.Tables["procs"].Rows)
                {
                    string procedureName = row["Procedure_Name"].ToString();
                    IDatabaseCommand procedure = _commandFactory.GetCommand(procedureName, Provider, _timeout, row.GetChildRows("p2p"));
                    _procedures.Add(procedureName, procedure);
                }
            }
            catch (DbException dbex)
            {
                throw new DataInterfaceException(dbex,
                                                 "DataInterface was unable to gather parameter data for all procedures. Check inner exception for more details.");
            }
            catch (DataException ex)
            {
                LogManager.LogError(ex, Severities.Critical,
                                    "An error occurred trying gather procedures from the database");
                throw;
            }
        }

        /// <summary>
        /// Add a stored procedure to call in the future. This function will query the
        /// database for parameter names and types and cache the results.
        /// </summary>
        /// <param procedureName="procedureName">The case-sensitive name of the stored procedure</param>
        public void AddProcedure(string procedureName)
        {
            if (!HasProcedure(procedureName))
            {
                try
                {
                    IDbCommand command = _provider.GetParamsForProcedureCommand(procedureName);
                    command.Connection = _provider.GetConnection(_connectionString);
                    DataTable parameterData = SetupCommand(command, procedureName)
                        .GetDataTable(Connection, procedureName);

                    IDatabaseCommand procedure = _commandFactory.GetCommand(procedureName, Provider, _timeout,
                                                                            parameterData);
                    _procedures.Add(procedureName, procedure);

                    if (_cache)
                    {
                        RemoveTable(procedureName);
                    }
                }
                catch (DbException dbex)
                {
                    throw new DataInterfaceException(dbex,
                                                     "DataInterface was unable to gather parameter data for procedure {0}. Check inner exception for more details.",
                                                     procedureName);
                }

                if (_cache)
                {
                    RemoveSet(procedureName);
                }
            }
        }

        /// <summary>
        /// Add a stored procedure to call in the future. The parameter names should be
        /// listed in the order you will assign values to them.
        /// </summary>
        /// <param procedureName="name">The case-sensitive name of the stored procedure</param>
        /// <param parameterNames="parameterNames">The case-sensitive names of the stored procedure's parameters</param>
        /// <remarks>Deprecated: when possible, use AddProcedure(string procedureName)</remarks>
        public void AddProcedure(string procedureName, params IDbDataParameter[] parameters)
        {
            if (!HasProcedure(procedureName))
            {
                var procedure =
                    _commandFactory.GetCommand(procedureName, Provider, _timeout, new List<IDbDataParameter>(parameters));

                _procedures.Add(procedureName, procedure);
            }
        }

        /// <summary>
        /// Adds a relationship to the specifed cached dataset.
        /// </summary>
        /// <param name="dataSetName">The name the dataset was cached by</param>
        /// <param name="relationName">The name of the relationship to create</param>
        /// <param name="parentTableName">The name of the parent table in the dataset</param>
        /// <param name="childTableName">The name of the child table in the dataset</param>
        /// <param name="parentColumn">The name of the parent table's column</param>
        /// <param name="childColumn">The name of the child table's column</param>
        /// <param name="nested">Specifies whether or not this relationship is nested.</param>
        /// <returns>A boolean indicated the success of adding the relationship</returns>
        public bool AddRelationToDataSet(string dataSetName,
                                         string relationName,
                                         string parentTableName,
                                         string childTableName,
                                         string parentColumn,
                                         string childColumn,
                                         bool nested)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataSet dataSet = _dataSets[dataSetName];
            DataColumn parentDataColumn = _tables[parentTableName].Columns[parentColumn];
            DataColumn childDataColumn = _tables[childTableName].Columns[childColumn];

            try
            {
                dataSet.Relations.Add(relationName, parentDataColumn, childDataColumn);
                return true;
            }
            catch (DataException ex)
            {
                throw new DataInterfaceException(ex,
                                                 "An error occurred attempting to add the relationship {0} between tables; {1} and {2}",
                                                 relationName, parentTableName, childTableName);
            }
        }

        /// <summary>
        /// Adds a relationship to the specifed cached dataset.
        /// </summary>
        /// <param name="dataSetName">The name the dataset was cached by</param>
        /// <param name="relationName">The name of the relationship to create</param>
        /// <param name="parentTableName">The name of the parent table in the dataset</param>
        /// <param name="childTableName">The name of the child table in the dataset</param>
        /// <param name="parentList">The name of the parent table's column</param>
        /// <param name="childList">The name of the child table's column</param>
        /// <param name="nested">Specifies whether or not this relationship is nested.</param>
        /// <returns>A boolean indicated the success of adding the relationship</returns>
        public bool AddRelationToDataSet(string dataSetName,
                                         string relationName,
                                         string parentTableName,
                                         string childTableName,
                                         List<string> parentList,
                                         List<string> childList,
                                         bool nested)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataSet dataSet = _dataSets[dataSetName];
            try
            {
                string[] parentColumns = new string[parentList.Count];
                string[] childColumns = new string[childList.Count];

                parentList.CopyTo(parentColumns);
                childList.CopyTo(childColumns);

                DataRelation relation = new DataRelation(relationName,
                                                         parentTableName,
                                                         childTableName,
                                                         parentColumns,
                                                         childColumns,
                                                         nested);
                dataSet.Relations.Add(relation);
                return true;
            }
            catch (DataException ex)
            {
                throw new DataInterfaceException(ex,
                                                 "An error occurred attempting to add the relationship {0} between tables; {1} and {2}",
                                                 relationName, parentTableName, childTableName);
            }
            return false;
        }

        /// <summary>
        /// Adds a cached table to the cached dataset. Both objects must already exist or an error will occur.
        /// </summary>
        /// <param name="dataSetName">Name of the cached dataset</param>
        /// <param name="tableName">Name of the cached table</param>
        public void AddTableToDataSet(string dataSetName, string tableName)
        {
            if (_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataSet dataSet = _dataSets[dataSetName];
            DataTable table = _tables[tableName];

            if (dataSet == null)
            {
                throw new DataInterfaceException(
                    "DataInterface cannot add table {0} to dataset because the dataset does not exist in the cache.",
                    tableName, dataSetName);
            }
            if (table == null)
            {
                throw new DataInterfaceException(
                    "DataInterface cannot add table {0} to dataset because the table does not exist in the cache.",
                    tableName, dataSetName);
            }

            if (!dataSet.Tables.Contains(tableName))
            {
                dataSet.Tables.Add(table);
            }
        }

        /// <summary>
        /// Adds the list of cached tables to the cached dataset. All tables and the dataset must already exist or an error will occur.
        /// </summary>
        /// <param name="dataSetName">Name of the cached dataset</param>
        /// <param name="tableNames">Names of the cached tables</param>
        public void AddTablesToDataSet(string dataSetName, params string[] tableNames)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            List<string> names = Reflector.GetGenericListFromArray<string>(tableNames);
            try
            {
                names.ForEach(CheckTable);
            }
            catch (DataInterfaceException dex)
            {
                throw new DataInterfaceException(dex, "The tables ({0}) could not be added to the dataset {1}",
                                                 DelimitedBuilder.Construct(tableNames, ","), dataSetName);
            }

            foreach (string tableName in tableNames)
            {
                AddTableToDataSet(dataSetName, tableName);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataSetName"></param>
        /// <returns></returns>
        public bool CreateDataSet(string dataSetName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            if (!_dataSets.ContainsKey(dataSetName))
            {
                _dataSets.Add(dataSetName, new DataSet(dataSetName));
                return true;
            }
            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataSetName"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public bool CreateDataSet(string dataSetName, params string[] tableNames)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataSet dataSet;
            DataTable table;
            if (!_dataSets.ContainsKey(dataSetName))
            {
                _dataSets.Add(dataSetName, new DataSet(dataSetName));
                dataSet = _dataSets[dataSetName];
                foreach (string strTableName in tableNames)
                {
                    try
                    {
                        table = null;
                        if (_tables.ContainsKey(strTableName))
                        {
                            table = _tables[strTableName];
                        }
                        if (table != null) dataSet.Tables.Add(table);
                    }
                    catch (DataException ex)
                    {
                        LogManager.LogError(ex, Severities.Critical,
                                            "An error occurred trying to create dataset {0} from table list: {1}",
                                            dataSetName, DelimitedBuilder.Construct(tableNames, ", "));
                        throw;
                    }
                }
                return true;
            }
            return false;
        }

        /// <summary>
        /// Executes the database (parameterized) command with the parameters provided with or without a transaction
        /// </summary>
        /// <param name="commandText">The text of the parameterized sql</param>
        /// <param name="useTransaction">Indicates whether or not to execute as a transaction</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteCommand(string commandText, bool useTransaction)
        {
            return ExecuteCommand(commandText, useTransaction, new List<IDbDataParameter>());
        }

        /// <summary>
        /// Executes the database (parameterized) command with the parameters provided with or without a transaction
        /// </summary>
        /// <param name="commandText">The text of the parameterized sql</param>
        /// <param name="useTransaction">Indicates whether or not to execute as a transaction</param>
        /// <param name="parameters">The parameters for the command</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteCommand(string commandText, bool useTransaction, List<IDbDataParameter> parameters)
        {
            return SetupCommand(commandText, parameters)
                .Execute(Connection, useTransaction);
        }

        /// <summary>
        /// Executes the database (parameterized) command with the parameters provided with or without a transaction
        /// </summary>
        /// <param name="commandText">The text of the parameterized sql</param>
        /// <param name="useTransaction">Indicates whether or not to execute as a transaction</param>
        /// <param name="parameters">The parameters for the command</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteCommand(string commandText, bool useTransaction, Dictionary<string, object> parameters)
        {
            return SetupCommand(commandText, parameters)
                .Execute(Connection, useTransaction);
        }

        /// <summary>
        /// Executes the database (parameterized) command with the parameters provided with or without a transaction
        /// </summary>
        /// <param name="commandText">The text of the parameterized sql</param>
        /// <param name="useTransaction">Indicates whether or not to execute as a transaction</param>
        /// <param name="parameters">The parameters for the command</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteCommand(string commandText, bool useTransaction, params object[] parameters)
        {
            return SetupCommand(commandText, parameters)
                .Execute(Connection, useTransaction);
        }

        /// <summary>
        /// Executes the database (parameterized) command with the parameters provided with or without a transaction
        /// </summary>
        /// <param name="query">The query object to translate and execute</param>
        /// <param name="useTransaction">Indicates whether or not to execute as a transaction</param>
        /// <param name="repositoryMapIndex">The repository specific map index to use for translation of type references</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteCommand(IQueryObject query, bool useTransaction)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return ExecuteCommand(commandText, useTransaction, parameters);
        }


        public DataTable ExecuteCommandWithTableResult(IQueryObject query, bool useTransaction, string tableName)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return CacheDataTable(
                SetupCommand(commandText, parameters)
                    .ExecuteWithTableResult(Connection, useTransaction, tableName)
                );
        }


        public DataSet ExecuteCommandWithSetResult(IQueryObject query, bool useTransaction, string datasetName, List<string> tableNames)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return CacheDataSet(
                SetupCommand(commandText, parameters)
                    .ExecuteWithSetResult(Connection, useTransaction, datasetName, tableNames.ToArray())
                );
        }

        /// <summary>
        /// Creates a new table from an existing cached table using the provided filter and store
        /// it in the cache by the alias.
        /// </summary>
        /// <param name="filter">The SQL filter to apply against the sourceTable</param>
        /// <param name="sourceTable">The alias of the source table to create the new table from</param>
        /// <param name="tableName">The alias of the new table to create from the rows mathcing the filter</param>
        public void ExtractTableByFilter(string filter, string sourceTable, string tableName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataTable dataTable = _tables[sourceTable];
            DataTable newDataTable = new DataTable();
            DataRow[] rows;
            DataRow newDataRow;


            foreach (DataColumn column in dataTable.Columns)
            {
                newDataTable.Columns.Add(column.ColumnName, column.DataType);
            }

            newDataTable.BeginLoadData();
            rows = dataTable.Select(filter);

            foreach (DataRow dataRow in rows)
            {
                newDataRow = newDataTable.NewRow();
                int indx = 0;
                foreach (object value in dataRow.ItemArray)
                {
                    newDataRow[indx] = value;
                    indx++;
                }
                newDataTable.Rows.Add(newDataRow);
            }
            newDataTable.EndLoadData();
            _tables.Add(tableName, newDataTable);
        }

        /// <summary>
        /// Returns an array of the rows matching the filter
        /// </summary>
        /// <param name="filter">The SQL filter to apply to the rows in the specified table</param>
        /// <param name="tableName">The alias of the cached table</param>
        /// <returns>A DataRow array containing rows which matched the filter</returns>
        public DataRow[] GetRowsByFilter(string filter, string tableName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            if (!HasTable(tableName))
                throw new DataInterfaceException("DataInterface has no cached table named {0}", tableName);
            return _tables[tableName].Select(filter);
        }


        public DataSet GetSet(string commandText, string datasetName, List<string> tableNames)
        {
            return CacheDataSet(
                GetSet(commandText, datasetName, tableNames, new List<IDbDataParameter>())
                );
        }


        public DataSet GetSet(string commandText, string datasetName, List<string> tableNames,
                              IEnumerable<IDbDataParameter> parameters)
        {
            return CacheDataSet(
                SetupCommand(commandText, parameters)
                    .GetDataSet(Connection, datasetName, tableNames.ToArray())
                );
        }


        public DataSet GetSet(string commandText, string datasetName, List<string> tableNames,
                              Dictionary<string, object> parameters)
        {
            return CacheDataSet(
                SetupCommand(commandText, parameters)
                    .GetDataSet(Connection, datasetName, tableNames.ToArray())
                );
        }


        public DataSet GetSet(string commandText, string datasetName, List<string> tableNames,
                              params object[] parameters)
        {
            return CacheDataSet(
                SetupCommand(commandText, parameters)
                    .GetDataSet(Connection, datasetName, tableNames.ToArray())
                );
        }

        public DataSet GetSet(IQueryObject query, string datasetName, List<string> tableNames)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return GetSet(commandText, datasetName, tableNames, parameters);
        }

        public IEnumerable<T> ProcessReader<T>(IQueryObject query, Func<IDataReader, IEnumerable<T>> process)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            IDatabaseCommand command = GetCommand(commandText);
            command.SetParameters(parameters);
            return command.ProcessReader(Connection, process);
        }

        /// <summary>
        /// Retrieves the cached dataset specified
        /// </summary>
        /// <param name="datasetName">The name of the dataset</param>
        /// <returns>Cached dataset</returns>
        public DataSet GetSet(string datasetName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            return _dataSets[datasetName];
        }


        public DataTable GetTable(string commandText, string tableName)
        {
            return CacheDataTable(
                GetTable(commandText, tableName, new List<IDbDataParameter>())
                );
        }


        public DataTable GetTable(string commandText, string tableName, List<IDbDataParameter> parameters)
        {
            return CacheDataTable(
                SetupCommand(commandText, parameters)
                    .GetDataTable(Connection, tableName)
                );
        }


        public DataTable GetTable(string commandText, string tableName, Dictionary<string, object> parameters)
        {
            return CacheDataTable(
                SetupCommand(commandText, parameters)
                    .GetDataTable(Connection, tableName)
                );
        }


        public DataTable GetTable(string commandText, string tableName, params object[] parameters)
        {
            return CacheDataTable(
                SetupCommand(commandText, parameters)
                    .GetDataTable(Connection, tableName)
                );
        }


        public DataTable GetTable(string tableName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            return _tables[tableName];
        }

        public DataTable GetTable(IQueryObject query, string tableName)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return GetTable(commandText, tableName, parameters);
        }


        public object GetValue(string commandText)
        {
            return SetupCommand(commandText, new List<IDbDataParameter>()).GetScalar(Connection);
        }


        public object GetValue(string commandText, IEnumerable<IDbDataParameter> parameters)
        {
            return SetupCommand(commandText, parameters)
                .GetScalar(Connection);
        }


        public object GetValue(string commandText, Dictionary<string, object> parameters)
        {
            return SetupCommand(commandText, parameters)
                .GetScalar(Connection);
        }


        public object GetValue(string commandText, params object[] parameters)
        {
            return SetupCommand(commandText, parameters)
                .GetScalar(Connection);
        }


        public object GetValue(IQueryObject query)
        {
            var parameters = query.Parameterize(_provider);
            var commandText = query.Translate(_provider);
            return GetValue(commandText, parameters);
        }

        /// <summary>
        /// Returns an array of values for a specifc column across all rows in the referenced table
        /// </summary>
        /// <param name="tableName">The alias of the table to pull the column values from</param>
        /// <param name="columnName">The name of the column to pull values from</param>
        /// <returns></returns>
        public object[] GetValuesByColumn(string tableName, string columnName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            DataTable table = _tables[tableName];
            object[] columnValues = new object[table.Rows.Count];
            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                columnValues[rowIndex] = table.Rows[rowIndex][columnName];
            }
            return columnValues;
        }

        /// <summary>
        /// Returns an array of values for a specifc column across all rows in the referenced table
        /// </summary>
        /// <param name="tableName">The alias of the table to pull the column values from</param>
        /// <param name="columnOrdinal">The ordinal index of the column to pull values from</param>
        /// <returns></returns>
        public object[] GetValuesByColumn(string tableName, int columnOrdinal)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            DataTable table = _tables[tableName];
            object[] columnValues = new object[table.Rows.Count];
            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                columnValues[rowIndex] = table.Rows[rowIndex][columnOrdinal];
            }
            return columnValues;
        }

        /// <summary>
        /// Returns the number of RecordsInTable (rows) in a given strTableName
        /// </summary>
        /// <param name="tableName">The strTableName name</param>
        /// <returns>The number of rows in a strTableName</returns>
        public int RecordsInTable(string tableName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }

            return _tables[tableName].Rows.Count;
        }

        /// <summary>
        /// Removes the specified stored set from the dataset cache
        /// </summary>
        /// <param name="datasetName">The alias of the dataset to remove from the cache</param>
        public void RemoveSet(string datasetName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            if (HasDataSet(datasetName))
            {
                _dataSets.Remove(datasetName);
            }
        }

        /// <summary>
        /// Removes the specified stored table from the table cache
        /// </summary>
        /// <param name="tableName">The alias of the table to remove from the cache</param>
        public void RemoveTable(string tableName)
        {
            if (!_cache)
            {
                throw new DataInterfaceException(
                    "Caching must be enabled to use this feature. Set CacheResults to true.");
            }
            if (HasTable(tableName))
            {
                _tables.Remove(tableName);
            }
        }

        #endregion

        #region Private Methods

        private DataSet CacheDataSet(DataSet dataSet)
        {
            if (_cache)
            {
                _dataSets[dataSet.DataSetName] = dataSet;
            }
            return dataSet;
        }

        private DataTable CacheDataTable(DataTable dataTable)
        {
            if (_cache)
            {
                _tables[dataTable.TableName] = dataTable;
            }
            return dataTable;
        }

        /// <summary>
        /// Used to check if the table exists in the cache, if not an error is thrown.
        /// </summary>
        /// <param name="tableName">Name/Alias of the table in the cache</param>
        private void CheckTable(string tableName)
        {
            if (!_tables.ContainsKey(tableName))
                throw new DataInterfaceException("Table {0} does not exist in the cache.", tableName);
        }

        private IDatabaseCommand GetCommand(string commandText)
        {
            IDatabaseCommand command;
            if (!commandText.Contains(" "))
            {
                if (!_procedures.ContainsKey(commandText))
                {
                    throw new DataInterfaceException("You must add the stored procedure, {0}, before calling it",
                                                     commandText);
                }
                command = _procedures[commandText];
            }
            else
            {
                command = _commandFactory.GetCommand(commandText, Provider);
            }

            return command;
        }

        private IDatabaseCommand SetupCommand(string commandText, IEnumerable<IDbDataParameter> parameters)
        {
            IDatabaseCommand command = GetCommand(commandText);
            command.SetParameters(parameters);
            return command;
        }

        private IDatabaseCommand SetupCommand(string commandText, Dictionary<string, object> parameters)
        {
            IDatabaseCommand command = GetCommand(commandText);
            command.SetParameters(parameters);
            return command;
        }

        private IDatabaseCommand SetupCommand(string commandText, params object[] parameters)
        {
            IDatabaseCommand command = GetCommand(commandText);
            command.SetParameters(parameters);
            return command;
        }

        private IDatabaseCommand SetupCommand(IDbCommand command, params object[] parameters)
        {
            IDatabaseCommand dbCommand = _commandFactory.GetCommand(command, _provider);
            dbCommand.SetParameters(parameters);
            return dbCommand;
        }

        /// <summary>
        /// Checks to see if the specified stored procedure has been added.
        /// </summary>
        /// <param name="procedureName">Case-sensitive name of the stored procedure.</param>
        /// <returns>A boolean which indicates whether the stored procedure has been added.</returns>
        private bool HasProcedure(string procedureName)
        {
            return _procedures.ContainsKey(procedureName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private bool HasTable(string tableName)
        {
            return _tables.ContainsKey(tableName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="datasetName"></param>
        /// <returns></returns>
        private bool HasDataSet(string datasetName)
        {
            return (_dataSets.ContainsKey(datasetName));
        }

        private void Initialize(IDbConnection connection, IDatabaseProvider provider)
        {
            Initialize(connection, provider, new DatabaseCommandFactory(), DEFAULT_TIMEOUT);
        }

        private void Initialize(IDbConnection connection, IDatabaseProvider provider,
                                IDatabaseCommandFactory commandFactory, int timeout)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            _connectionString = connection.ConnectionString;
            _provider = provider;
            _commandFactory = commandFactory;
            _timeout = timeout;
        }

        #endregion

        #region Constructors

        /// <summary>
        /// Creates a DataInterface object using a pre-filled connection object.
        /// </summary>
        /// <param name="connection">The connection object to create this instance with</param>
        public DataInterface(IDbConnection connection, IDatabaseProvider provider)
        {
            Initialize(connection, provider);
        }

        /// <summary>
        /// Creates a DataInterface object using a pre-filled connection object.
        /// </summary>
        /// <param name="connection">The connection object to create this instance with</param>
        /// <param name="timeout">The timeout, in seconds, to use for procedure calls.</param>
        public DataInterface(IDbConnection connection, IDatabaseProvider provider, int timeout)
        {
            Initialize(connection, provider, new DatabaseCommandFactory(), timeout);
        }

        /// <summary>
        /// Creates a DataInterface instance from the providerTypeName and the connection string
        /// </summary>
        /// <param name="providerTypeName"></param>
        /// <param name="connectionString"></param>
        public DataInterface(string providerTypeName, string connectionString)
        {
            var provider = DatabaseProviderFactory.GetProvider(providerTypeName);
            var connection = _provider.GetConnection(connectionString);
            Initialize(connection, provider);
        }

        /// <summary>
        /// Creates a DataInterface object using the ConnectionStringSettings object from
        /// the ConnectionStringSection in the web or app config.
        /// </summary>
        /// <param name="connectionSettings"></param>
        public DataInterface(ConnectionStringSettings connectionSettings)
        {
            var provider = DatabaseProviderFactory.GetProvider(connectionSettings.ProviderName);
            var connection = provider.GetConnection(connectionSettings.ConnectionString);
            Initialize(connection, provider);
        }

        /// <summary>
        /// Internal implementation for dependency injection of all dependent objects.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="provider"></param>
        /// <param name="commandFactory"></param>
        internal DataInterface(IDbConnection connection, IDatabaseProvider provider,
                               IDatabaseCommandFactory commandFactory)
        {
            Initialize(connection, provider, commandFactory, DEFAULT_TIMEOUT);
        }

        #endregion

        #region Implementation of IDisposable

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        /// <filterpriority>2</filterpriority>
        public void Dispose()
        {
            _dataSets.Clear();
            _dataSets = null;
            _provider = null;
            _procedures.Clear();
            _procedures = null;
            _tables.Clear();
            _tables = null;
        }

        #endregion
    }
}